Cleaning Excel Data With
Power Query
Straight to the Point

Oz du Soleil

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Cleaning Excel Data with Power Query Straight to the Point

© 2019 by Tickling Keys, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Published: March 2019
Author: Oz du Soleil

Copyeditor: Kitty Wilson

Cover Design: Suat M. Ozgur

Cover Illustration: Shannon Mattiza, 6'4 Productions

Indexer: Nellie Jay

Tech Editor: Bill Jelen

Screen Reader Captions: Deb Govern

Compositor: Jill Cabot

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-250-5 PDF, 978-1-61547-371-7 ePub, 978-1-61547-149-2 Mobi

Table of Contents

About the Author1

Acknowledgments1

INTRODUCTION1

So, What Is Power Query?2

When I Became a Believer…2

FIRST, SOME BACKGROUND FOR POWER QUERY3

Excel Versions3

Everything Happens via Queries3

Data Types5

Power Query Warnings5

GETTING DATA INTO POWER QUERY7

Importing Data from a Table or Range7

Importing Data from a Workbook8

FILL UP AND FILL DOWN12

PARSING TEXT16

Splitting Names by Delimiter17

Splitting into Rows20

SORTING IN POWER QUERY22

MERGING DATA: JOINING AND APPENDING25

Joins and Merging25

Full Outer Join: Creating a Master List33

Inner Join: Determining Who Registered and Attended38

Right and Left Anti Joins: Determining What’s over Here That’s Not over There43

RIGHT ANTI JOIN43

LEFT ANTI JOIN43

Left and Right Outer Joins: Power Query’s VLOOKUP44

LEFT OUTER JOIN44

RIGHT OUTER JOIN44

Appending Datasets45

GROUPING DATA47

REFRESHING AND UPDATING DATA49

ADDING COLUMNS50

Adding Conditional Columns50

Adding Multiple Conditions52

Adding Custom Columns55

Adding Custom Columns with Power Query Functions and Adding Whole Numbers to Dates58

Adding a Column Using Column from Examples59

CONCLUSION63

About the Author

Oz du Soleil is an Excel MVP who’s been working with Excel since 2001. He’s co-author, along with Bill Jelen, of Guerrilla Data Analysis, 2nd edition. Oz has several Excel courses on the LinkedIn Learning platform. He’s possibly best known for the dramatic and colorful Excel tutorials he posts on his YouTube channel Excel on Fire.

Data cleansing is Oz’s area of specialization in Excel. From his earliest days with Excel, he has found himself constantly needing to fix names that are ALLCAPS, peel addresses away from phone numbers, clean up the messes that result from extracting data from PDF files,…and fixing all the many other things that prevent data from being useful.

Most recently, Oz has presented Excel topics and master classes at conferences in Amsterdam; Sofia, Bulgaria; São Paulo, Brazil; Toronto, Canada; and cities around the United States.

When Oz isn’t elbows-deep in the guts of a spreadsheet, he does storytelling around Portland, Oregon. He has told stories onstage for Risk!, Pants on Fire, Seven Deadly Sins, Pickathon, The Moth, and other storytelling shows.

Acknowledgments

Thanks to Bill Jelen for giving me the opportunity to share my favorite part of Excel with you. Thanks to the teams at Microsoft who built Power Query for Excel. Special thanks to Guy Hunkin, who has taken special interest in how we use Power Query and how it can be made even better.

Ken Puls and Miguel Escobar have been especially valuable in sharing knowledge about Power Query through blog posts and open challenges that help the community make better use of this tool.

Finally, thanks to the people who have been especially supportive of my efforts over the years, including my YouTube subscribers and the community of Excel MVPs. One day we’re going to get this world’s data clean!

INTRODUCTION

This book provides a quick Straight-to-the-Point introduction to Power Query. There’s a lot here, and there’s also a lot that’s not here. Reading this book will be like sitting down at a café and striking up a conversation with the person at the next table, where you learn enough random stuff about that person to decide if you want to know more and keep in touch. In this case, of course, you’ll want to know more about Power Query. And there are lots of resources for digging deeper into it.

This book is designed to give you just what you need to experiment with Power Query because that’s a great way to learn. I encourage you to play! Make up your own datasets and see what happens. To download the data sets used in this book, visit https://mrx.cl/ozdatapq.

So, What Is Power Query?

A crucial piece of data science, big data, data visualization, predictive analytics, machine learning, and other hot topics is that the dirty work of data cleansing must be performed before the glamorous work can start. Original data comes to us in a variety of messy formats, some of which are deliberate. Data may be sprinkled in multiple locations, such as stacked in columns; you might find a person’s ID number in one source and her name in another source; columns of data may not match up; and you may encounter dozens of other messy situations that require cleanup.

Power Query helps you retrieve, cleanse, and transform data to make it useful. Power Query also automates tedious processes that might consume days each week if handled manually.

When I Became a Believer…

One afternoon I was working with a client’s project, and the data that came to me looked like the data you see below, on the left. This data shows hours worked at a law firm, and you can see that Mel worked 130 hours for Joram in February. There’s a lot of useful data here. The problem is that it’s in a report form for one specific view.

I wanted to convert the original matrix to sortable columns and rows, like what you see on the right. By making the data sortable, I knew I could access other things:

Each attorney’s totals by month—no matter for which law partner (which might be important if I needed to sum all the hours Mel worked for two partners over the course of three months)

Who has worked over 550 hours, if anyone

Each law partner’s total consumption of hours, by month

I thought about all the possible ways I could make this data sortable, such as by using clumsy formulas or dragging the data around manually. I remembered a YouTube video that MrExcel made on “unwinding data,” and I thought about looking that up. And then I remembered seeing the term unpivot somewhere in Power Query. Since the matrix looked like a pivot table that needed to be unpivoted, I wondered if that’s what I needed. Yes! Unpivot was the solution, and it worked instantly!

That’s the day I embraced the gospel of Power Query.

FIRST, SOME BACKGROUND FOR POWER QUERY

Before we dive into Power Query examples, there are a few things you need to know.

Excel Versions

How you access Power Query depends on the version of Excel you’re running:

In Excel 2016, Power Query is in the Data tab of the ribbon: It’s the unintuitively named Get Data option in the Get & Transform Data group. (I won’t get into the history of why this ribbon option is not actually called Power Query, but I’ll assure you that Microsoft is gradually working the term Power Query back into its products.)

In Excel 2010 and 2013, Power Query is an add-in that must be downloaded from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=39379.

Currently, Power Query is not available for Mac versions of Excel.

Everything Happens via Queries

It is not likely that your source data is in Power Query. Data is typically imported into Power Query from various sources such as tables, other workbooks, folders, databases, Facebook, and SharePoint.

When you’re inside the Power Query Editor, you can build queries. Steps are recorded as you transform your data. In the image below, under Applied Steps, Split Column by Delimiter was added after I used the interface to split a column. Think of Applied Steps as a modern Macro Recorder with two differences from the old Macro Recorder. First, Applied Steps records in a language called M instead of VBA. Second, Applied Steps actually works reliably, whereas the VBA Macro Recorder rarely recorded production-ready code.

Although this grid looks a lot like the Excel grid, there is one major difference: You can’t just click in a cell here and type a correction. Corrections to the data must happen at the source. For example, in the image below, say that you realize that in row 1 Lindy should actually be Linda. You cannot type the change here to correct it. You have to fix it in the workbook, worksheet, or table the data came from. If you realize that Lindy should be Linda in this one instance, you should head back to Excel and correct the data there. If, however, you need to globally change every Lindy to Linda, you can use Replace Values in Power Query to make the change to the column.

Data Types

Power Query is very strict about data types. Notice in the image below that there are icons to the right of the column headers. These icons tell you what each data type is:

ABC icon = text

123 icon = whole numbers

Calendar icon = dates

ABC123 icon = a mix of numbers and text

If you look at the Custom column, you can begin to understand the importance of data types. That whole column errored out when I added together the Visits and Birthday columns. On an Excel worksheet, you would get a result if you tried to combine this data. But Power Query says, “No, no, no, I’m not adding a date and a whole number together!” and it kicks out an error.

If you want to add a whole number to a date, Power Query forces you to take a deliberate action and specify whether that whole number represents days, months, or years. (You will learn more about this in the section "Adding Custom Columns with Power Query Functions and Adding Whole Numbers to Dates" on page 58)

Power Query Warnings

Power Query makes a lot of things easy, but you have to be wary. Some things aren’t possible inside the Power Query Editor, and some tasks that are easy on an Excel worksheet are complicated inside Power Query. Therefore, it’s important to be strategic and think several steps ahead to anticipate what might be best handled in a worksheet versus in the Power Query Editor. Keep in mind these warnings:

You cannot operate at the row or cell level. Whatever you do in your transformations will happen to the whole dataset or to an entire column.

Importing data into Power Query eliminates formatting such as font color, boldface, cell color, and borders. It is important to know this in some situations. For example, in the image below, Excel uses font color to distinguish between the name of a city and the name of a person. When you bring this data to Power Query, as shown at the far right, there is no visual clue that distinguishes a city, such as Hong Kong, from a person’s name, such as Romona Lloyd.

A blank cell, null, and 0 are not the same thing. In the example below, Clyde La Croix has a 10 and 12 that should be added in the Total column as 22. Instead, Power Query shows the total as null. Why? Power Query doesn’t see null as a 0.

GETTING DATA INTO POWER QUERY

How do you get data into Power Query so that you can start taking advantage of its magic? There are various ways of importing data. The main way is from a worksheet in a table or range of data.

Importing Data from a Table or Range

Say that, as shown in the image below, you have a range of addresses and need to send the data into Power Query. Here’s what you do:

1. Place your cursor in the data range.

2. On the Data tab of the ribbon, select From Table/Range. The Create Table dialog box appears.

3. Verify that the data you want has been selected. In this case, the green dotted line around the dataset shows that, yes, the complete dataset B1:E14 has been selected.

4. Select the checkbox My Table Has Headers to let Power Query know that the table has headers.

5. Click OK.

Now your data is inside Power Query, as you can see below.

Note: This book primarily uses the method just shown for importing data. For the sake of brevity, whenever a data import is required, we skip the details of importing from a table or range and show the data already inside the Power Query Editor.

Importing Data from a Workbook

Importing from a workbook is really cool for a lot of reasons:

Sometimes you need to bring data together from multiple workbooks, and Power Query allows you to skip any copied/pasted or linked workbooks that contain delicate formulas.

Data may not neatly fit into a table, as in the example below. Here you can see empty rows and columns, as well as headers and symbols. There is too much going on to try to include it all in a table. A better strategy would be to save this data in its own workbook, which you could call, for example, Member Addresses.

To import the data shown above so you can work on it in Power Query, follow these steps:

1. Open a new workbook and then, from the Data tab, select Get Data > From File > From Workbook.

2. Navigate to the Member Addresses file and click Import. The Navigator opens.

3. Tell Power Query whether you want to import the whole workbook, a specific worksheet, a table, or something else. In this case, select Sheet1, and you get to see a preview of the dataset before you commit to the import.

The Navigator dialog has a list of sheet names and range names on the left. Click Sheet1 and a preview of data appears on the right. At the bottom right are choices for Load, Edit, or Cancel.4. Click Edit so that the data goes into Power Query and not the worksheet. (You don’t want it there yet.) As shown below, you now see the data in Power Query! The entirety of Sheet1 in the Member Addresses workbook is ready for any transformations you need to make.

Next, you can start cleaning and transforming the data.

FILL UP AND FILL DOWN

One common need when you work with data is to fill holes in a dataset. For example, say that you have a report that contains details about four office locations. You can see below that Alesha works at 19 Corporate Center, her supervisor is Elise, and there are 100 employees at that location.

Say that you’d like to see this data in different ways:

You want to sort the Leadership Team column in alphabetical order and keep each person connected to the correct manager and office.

You want to match each office with the number of employees.

Excel does have Fill Up and Fill Down features, but they don’t respect changes in the data. If you were to apply Fill Down to C3:C18, for example, the entire column would become Mt. Hood Park, from top to bottom, completely erasing the other three offices. Instead, to get the results you want, you can use Power Query.

With the dataset in Power Query, you can fill holes in this dataset by following these steps:

1. Highlight the Manager column.

2. Right-click and select Fill > Down.

3. The Manager column is now correctly filled in, as shown below.

4. Do the same thing to fill in the Office column with Fill Down.

5. Highlight the Total Employees column. Right-click and use Fill, Up.

6. Right-click and select Fill > Up.

7. To sort the Leadership Team column in alphabetical order, highlight the column.

8. In the Home tab of the ribbon, click the A-Z icon.

PARSING TEXT

Parsing strings of data is one of the most common and most irritating things you have to deal with when cleansing data. Power Query makes the process downright sexy. You could separate first and last names as shown below. You might think that it would then be easy to separate the names with Text-to-Columns or Flash Fill because the comma provides a consistent separation between the names.

Last Name, First Name

Angus, CFP, CPA, Lindy

Reynolds, Stephanie

Tucker, DDS, Juliet

But then, as you can see below, trying to get from the original dataset in column B to the result in columns E and F is a pain in Excel.

In column B, those commas separating the names, professional designations, and suffixes…Ouch!

Text-to-Columns would make a big mess here, and the traditional alternative is a complex formula using the SUBSTITUTE function. No thanks!

But Power Query gives you multiple ways of easily splitting data, as discussed next.

Splitting Names by Delimiter

To split names by delimiter, follow these steps:

1. On the Home tab, select Split Column > By Delimiter.

2. The Split Column by Delimiter box defaults to splitting by comma, but in this case, click the arrow to open the dropdown list and choose Custom.

3. Type in a comma and a single space as a custom delimiter, as shown below. (You will be able to get rid of both the comma and the space later on, as opposed to splitting by the comma and having a space to clean or trim later.)

4. Under Split At you have three brilliant options that you don’t get with Text-to-Columns. In this case, choose Right-Most Delimiter because that’s what separates the first name from the last name and professional designations.

5. Click OK, and you see the result shown below.

6. Now you can double-click the headers to change the titles and drag the columns to switch their positions, as shown below. This is just what you wanted!

Splitting into Rows

Sometimes you face the dreaded “everything crammed into one cell” problem, where data has been pasted into Excel from a web page or an email, and it looks like this:

In this case, it would be nice to have each student in his or her own cell in one column and matched with the session attended. Here’s how you do this:

1. In Power Query, highlight the Students column.

2. On the Home tab, select Split Column > By Delimiter.

3. In the Split Column by Delimiter dialog, select Custom as the delimiter and type in a comma with a space after it. (As mentioned earlier, if you split with just a comma, you’ll need an additional step to clear the spaces. This way, you can handle everything all at once.)

4. For Split At select Each Occurrence of the Delimiter.

5. Click the triangle to expand the Advanced Options section and under Split Into, select Rows.

6. Leave the Quote Character setting as is.

7. Click OK.

Below you can see the glorious result!

Did you notice? The session values were automatically paired with the names. You didn’t have to do a Fill Up or Fill Down after splitting the rows!

Now you can sort by names or see if any students were in more than one session. If you have a list of the students’ contact information, you can also match up that information.

SORTING IN POWER QUERY

In Power Query, sorting is a bit different than in native Excel. On the File tab of the Power Query ribbon is a Sort button like the one in Excel that you’re familiar with. However, the Power Query Sort button just sorts. You don’t get a dialog box with an assortment of options. So, the obvious question is: How can you sort multiple levels? Answer: Carefully!

You have to sort columns in the order in which you want them sorted. In the dataset shown below, for example, you have Names, Directors, and Hours columns, and you might want to sort three levels:

By Director, smallest to largest

By Hours, largest to smallest

By Name, smallest to largest

If you were to use the AZ and ZA buttons in Excel, you would have to sort by Name first, then Hours, then Director. But the process in Power Query is reversed.

To do this, you must select each column and then sort it, select and sort the next column, and so on:

1. Highlight the Director column and sort A-Z.

2. Select the Hours column and sort Z-A.

Note: Even though you have done two Sort operations, the only one step appears in Applied Steps. The formula bar is building a multi-level sort because the sort operations were performed in sequence.

3. Finally, select the Name column and sort A-Z.

The result:

Note: If you look at the result of three sorts in View, Advanced Editor, the three operations created on step: #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Director", Order.Ascending}, {"Hours", Order.Descending}, {"Name", Order.Ascending}}). Because you carefully performed the sort in the proper sequence, Power Query built a multi-level sort.

MERGING DATA: JOINING AND APPENDING

This section is one I suggest studying carefully because much of what you do with data involves merging, comparing, and stacking lists.

Joins and Merging

Here are some ways you might want to use joins and merging:

To determine what’s on list A that’s not on list B

To match the email addresses from list B with the right people on list A

To create a master list of people who are on two different lists and match up the ones that are present on both lists

Power Query gives you six joins to help you with these needs:

Full Outer Join

Left Outer Join

Right Outer Join

Inner Join

Right Anti Join

Left Anti Join

No, the names of the joins aren’t intuitive. (A Left Anti Join is anti the left of what?) And when you look at the interface, there is no left or right.

Thanks to a blog post by Excel MVP Ken Puls, I came to understand what all the Inner, Outer, Anti, Left, and Right stuff is about. Here I detail a few of the six joins and leave the rest for you to explore on your own.

To see the join options in action, you need two lists where it’s possible for entries to be on either list or both lists. Your lists could be, for example, people who attended an event and people who registered online.

With a quick glance at these two lists, it’s easy to see a few details:

1. Al and Thomas attended but didn’t sign up online.

2. Millicent registered online but didn’t attend.

3. Cassie, Cricket, and Jorge both signed up online and attended.

Now let’s view the lists a different way:

If you make this view of the lists into a Venn diagram, as shown above, the left, right, and inner distinctions start to make sense.

Before you can start using joins, you need to create queries. In this, case you want to load them as connections. Follow these steps:

1. With the cursor in the Attended list in Power Query, on the Data tab select From Table/Range.

Because the list is already in a table, you are taken straight into Power Query.

Note: you could click in the Name box in Properties and rename Table2 to Attended. This is an alternate to having to rename the query later.2. For the sake of this example, you don’t need to modify the data, so click the down arrow next to Close & Load and select Close & Load To.

3. In the Import Data dialog that appears, select Only Create Connection and click OK. The query is created.

Note: You should typically select Only Create Connection when the result of the query doesn’t need to be visible in the workbook. In this example, you are making a query for the purpose of being able to merge it with another query. You don’t need to see the intermediate queries, and they would just create clutter in the workbook.

4. To give the query a useful name, in the Queries & Connections pane, right-click the query you just created. Here, it’s called Table2. Select Rename and call it Attended.

After renaming, you have one properly named query in the Queries & Connections pane.

5. Repeat steps 1–4 to make the query for the list of people who registered online and name that query Registered.

With both queries created, you can start merging, joining, and digging into the data.

Full Outer Join: Creating a Master List

The Full Outer Join option brings two lists together and matches all entries that have matches. Here’s how you use it:

1. From the Data tab, select Get Data > Combine Queries > Merge.

2. Notice that in the Merge pane, shown below, the default is Left Outer join. Click the Join Kind down arrow and select Full Outer.

Note: In this figure, I’ve added the LEFT and RIGHT labels to show that when you use the Left Outer, Right Anti, and other options, the top of the Merge pane is considered left, and the bottom is right.

3. Select the Attended column and the Registered Online column so that Power Query knows which columns to try to match.

4. Click OK. As you can see below, it is easy to see what the left and right sides are: The left side is the Attended query, and the right side is the Registered query.

5. To see the data in the Registered query that is collapsed, expand it by clicking on the arrows that are facing away from each other, as shown below.

6. Deselect the checkbox Use Original Column Name as Prefix and click OK.

The result is shown below.

Bonnie is in row 1 and listed only in the Registered Online column. In the Attended column she has the value null because she didn’t attend the event. Jorge, however, is listed in both columns because he both attended and registered online. From these results, you can see that of the 17 unique people, 11 attended and 12 registered online. Full Outer Join helped by making matches (rather than, for example, stacking one list on the other and giving you 11 + 12 = 23).

7. Call your new query Fans.

8. In the Query Settings pane, under Properties and Name, replace Merge1 with Fans.

9. Click Close & Load.

Power Query sends the final dataset to a new worksheet, and you have what you wanted: a list of everyone who either attended or registered online—or both.

Note: In "ADDING COLUMNS" on page 50, you’ll see how you can merge these two columns into a single list.

Inner Join: Determining Who Registered and Attended

You can use Inner Join to isolate the people in the middle of the Venn diagram—that is, the people who appear on both lists.

You start the same way you did with Full Outer Join in the previous section:

1. From the Data tab of the ribbon, select Get Data > Combine Queries > Merge. In the Merge pane, you see Attended at the top, which, you’ll recall, is considered the left side; Registered goes on the bottom, which is considered the right side.

2. Under Join Kind, select Inner.

3. Click OK.

The data is now in the Power Query Editor, as shown below. Take a moment to think about what you’ll see after you expand the Registered column. Since you’re looking only for the people who are on both lists, you can expect the two lists to be identical.

4. Click on the arrows that are facing away from each other to expand the Registered column.

5. To clean this up and reduce it to a single column, highlight the Attended column, right-click the column header, and select Remove.

6. Double-click the column header and rename the column Registered & Attended.

7. Click Close & Load.

8. Finish up by renaming the query RegisteredAttended.

Right and Left Anti Joins: Determining What’s over Here That’s Not over There

An Anti Join between two tables returns rows from one table where no matches are found in the other table. You can therefore use the Anti Join option to isolate entries that are on one side but that are not on the other side. To use Right Anti Join and Left Anti Join, follow the same basic steps as for Full Outer Join and Full Inner Join, but in the Merge pane, select the appropriate kind of Anti Join under Join Kind.

RIGHT ANTI JOIN

The image below represents a Right Anti Join, which tells you who’s on the right side but not on the left side—in this case, who registered and did not show up.

LEFT ANTI JOIN

The image below represents a Left Anti Join: people who just waltzed right in, unannounced. They did not register but attended anyway.

Left and Right Outer Joins: Power Query’s VLOOKUP

You can think of Outer Joins as being the Power Query equivalent of VLOOKUP. To use these join types, follow the same basic steps as for Full Outer Joins and Full Inner Joins, but in the Merge pane, select either Left Outer Join or Right Outer Join under Join Kind.

LEFT OUTER JOIN

The image below represents a Left Outer Join. Starting with the left side, it returns only the items in the right side that have a match: everyone who attended, whether they registered or not.

RIGHT OUTER JOIN

The Right Outer Join shown below isolates everyone who registered, whether they attended or not.

Appending Datasets

How do you get two datasets stacked up so that you can use all the information? The following two datasets show inventories of acrylic paint that are in two different stores. Say that you would like to get them stacked on top of each other and have the columns line up.

Note that the columns in these two lists are not the same, and the items in each list are not in the same order. Neither of these issues is a problem for Power Query’s Append feature: As long as the column headers have the same header names, Append will line them up.

The image below shows the queries, named Store1 and Store2, already created.

To use Append to stack these datasets on top of each other and have the columns line up, follow these steps:

1. From the Data tab, select Get Data > Combine Queries > Append. The Append dialog box opens.

2. Use the dropdown lists to tell Power Query which table should go on the top. In this case, make Store1 the primary table and append Store2 to it.

3. Click OK.

As you can see below, the data is all stacked up. Where the headers matched, Append lined them up. It also created a SKU column for the data in the Store1 query.

GROUPING DATA

We can use the data from the previous example to now explore grouping in Power Query. Say that you want to get sums of the inventory data to determine how many 8-ounce tubes of black paint, 3-ounce tube of gold paint, and so on you have. Follow these steps:

1. On the Home tab, select Group By. The Group By dialog box appears.

2. Select Advanced to group by two criteria.

3. Click the Add Grouping button to get a second option.

4. From the Group By dropdowns, select Colors and Size.

5. For New Column Name enter Total.

6. For Operation select Sum.

7. For Column select Inventory.

8. Click OK.

As you can see here, the SKU column is gone (which is fine because you don’t need it in this case). The original 12 rows have been reduced to 8 rows. Between them, the two stores have a total of 11 3-ounce tubes of Pale Yellow paint.

If you now click Close & Load, the data appears as shown below in the workbook.

REFRESHING AND UPDATING DATA

To look at refreshing and updating data in Power Query, we can continue on from the previous dataset. Say that there is now additional data: Store 1 has more Purple and Brick Red paint, and Store 2 has more Tangerine paint and some Electric Green paint.

To update the totals and integrate the new data, on the Data tab, click Refresh All. You don’t have to repeat the Append or even go back into the Power Query Editor, but everything updates, as shown below—the new colors, the new inventory numbers, everything!

ADDING COLUMNS

Power Query allows you to add a new column to your data. Excel pros are often frustrated that the familiar functions from Excel are completely different in Power Query. Whereas you use =LEFT(A2,7) in Excel, the equivalent Power Query function is Text.FromLeft([Colors],7). If you don’t type uppercase and lowercase exactly correctly, you get an error. The following sections detail some of the useful new column types.

Adding Conditional Columns

In Excel, IF statements work on conditions; conditional columns in Power Query can be thought of as visual IF statements. To see how to add conditional columns, let’s go back to the Joins workbook and the Fans query. Say that you want to add a conditional column to consolidate the two columns into one so that you have a master list of everyone who’s contacted you—whether at an event or through email. Follow these steps:

1. From the Add Column tab of the ribbon, select Conditional Column.

2. The Add Conditional Column dialog box appears.

3. Name the new column All Contacts.

4. Use the dropdown lists to create a condition that says “If the Attended column is null, then capture the Registered Online value; otherwise, capture the Attended value.”

5. Click OK. You now see an All Contacts column.

Adding Multiple Conditions

To see how to add multiple conditions, say that you have an inventory of items and would like to generate a Status column that shows the following:

Critical: Items that are below the threshold

Re-Order: Items that are equal to the threshold level

Null: All other items

Follow these steps:

1. From the Add Column tab of the ribbon, select Conditional Column.

2. Enter the criteria shown below. (To add the second condition, click the Add Rule button.)

3. Click OK. You now have the Status column you wanted!

4. Finally, you can filter the Status column to get rid of the nulls, delete the On-Hand and Low Level columns, and load the final list to the workbook. The result is shown below.

Adding Custom Columns

Custom columns are more sophisticated than conditional columns because you can use them to do math and build conditions that go beyond the basic “equal to,” “starts with,” “greater than,” and so on that are available with conditional columns. However, creating custom columns requires writing formulas.

This next example is a twist on the previous one. You can see that there is a column for Low Level, and the inventory is split between the East and South warehouses. Say that you want to create an alert that warns you when it’s time to reorder. To do this, you can create a column called Alert Level that finds the level that is 1.5 times the Low Level value.

Follow these steps:

1. From the Add Column tab of the ribbon, select Custom Column. The Custom Column dialog box appears.

2. Type a column name of Alert Level.

3. To create the formula in the image below, double-click Low Level in the Available Columns pane, and Low Level automatically appears in the formula, with the brackets and everything.

4. Type *1.5 and click OK. You now have your Alert Level column.

You can add a Total column by adding the East Warehouse and West Warehouse values together in another custom column. To do this, follow these steps:

1. From the Add Column tab of the ribbon, select Custom Column. The Custom Column dialog box appears.

2. Type a name of Total in the New Column Name box.

3. Double-click South Warehouse in the Available Columns list, type the +, and double-click East Warehouse. Power Query properly wraps the field names in square brackets. Click OK.

Now you can see in row 7 that for item AM-273, the Low Level is 10, and you have 11 total. In addition, the Alert Level column value of 15 lets you know that you have 1 available before you’re in trouble.

Adding Custom Columns with Power Query Functions and Adding Whole Numbers to Dates

Earlier in the book, I mentioned the importance of data types. In this example, you’ll see why I was careful to warn about that. Say that you have members, courses, the time that each member has for completing a course, and the purchase date. You’d like a column that tells you the due date.

The first thing you have to deal with is the Purchase Date column because it’s showing as date/time, and you want just a date.

Follow these steps:

1. Click on the calendar/clock icon to open the dropdown list and select Date.

2. Add a custom column named Due Date. To add the time period to the purchase date, you use the Date.AddDays function. (As you might guess, there are also Date.AddMonths and Date.AddYears functions.)

Here is the result:

Adding a Column Using Column from Examples

In this example, say that you have residents, buildings, and units. Your objective is to merge the Building and Unit columns, separated by a hyphen (for example, A-1).

Follow these steps:

1. In the Add Column tab, click Column from Examples. You now see a pane where you can type in an example of what you want.

2. In the first row in Column 1, type A-1 and press Enter. Notice the grayed-out data filling the column below row 1. That is Power Query’s way of guessing at your desired result, asking you to make sure it’s what you want before committing to it. In this case, it’s exactly what you want.

Notice that at the top of the screen, Power Query is building a formula based on what it sees in your example. Click OK to accept the results. As shown below, the new column is automatically titled Merged. You can double-click this header to name the column something else.

Note: At times, Column from Examples needs more than one example in order to be trained. In the example shown below, I had a Category column and a Part Number column. My desired format in a new column was the category followed by four digits (for example, F-0019). Notice the numbers that are not grayed out: rows 1 and 4 in the custom column. I had to give two examples before Column from Example generated the desired result. After I typed C-0001, Power Query guessed that I wanted to add three 0s before every part number (for example, F-00019, L-000204). After I fed Power Query two examples, however, it accurately created the desired result.

After several updates, the Column from Examples has evolved to become a pretty good tool.

UNPIVOTING DATA

Say that you have data in a matrix that is designed to let a human being see that, for example, Earl scored 84 in Module 3. You could, of course, get a lot more from this dataset if the scores were sortable.

The easiest way to get this data unpivoted is to bring it into Power Query:

1. From the Data tab, select Get Data > Table/Range.

2. In the Power Query Editor, highlight the Name column. On the Transform tab, open the Unpivot Columns drop-down menu and choose Unpivot Other Columns.

Note: The Name column is okay as it currently is because the names are already in a column. The scores and the modules are what need to be flattened out. That’s why you highlight the Name column and unpivot the other columns.

The result is shown below. Isn’t it beautiful? Power Query immediately puts the scores and modules in columns, and you can sort, filter, join,…whatever you need to do with this data!

CONCLUSION

There’s a lot in Power Query. This short book doesn’t have room to cover topics like filtering, M code, and appending multiple columns, but there’s a helluva lot that is covered here, and hopefully you have found the journey worthwhile. Now make up your own datasets and go play!

To learn more about Power Query, check out M is for (Data) Monkey by Ken Puls and Miguel Escobar.

Index

A

Adding columns 50

Column from Examples 59

Custom column 55

Multiple conditions 52

Add rule 53

Advanced editor 25

Appending data sets 45

C

Column from Examples 59

Combine Queries 33

Custom column 52

D

Data cleansing 2

Date.AddDays 58

E

ElseIf 53

Error column 5

Escobar, Miguel 1

Excel versions 3

Expand icon 36

F

Fill Down 12

Flash Fill 16

From workbook 9

Full Outer Join 33

G

Grouping data 47

I

Import Data dialog 29

Importing data

from Table/Range 7

from Workbook 8

Inner Join 38

L

Left anti join 43

LEFT function 50

Left outer join 44

M

Mac Excel 3

Macro recorder 3

Move columns 19

MrExcel 2

N

Null versus zero 6

O

Only Create Connection 29

P

Parsing text 16

Portland Oregon 1

Power Query

Add-In 3

Join types 25

Warnings 5

Puls, Ken 1, 26

R

Refresh All 49

Refreshing queries 49

Renaming columns 19

Replace values 4

Right anti join 43

Right outer join 44

S

Sorting data 15

Multiple levels 22

Split column 17

Split to rows 20

Stacking data sets 45

SUBSTITUTE function 17

T

Text.FromLeft 50

Text-to-columns 16

U

Unpivoting data 62

Unwinding data 2

V

Venn diagram 27

Versions 3

W

Warnings 5

Straight to the Point

Have a topic that you would like to write about for the Straight to the Point series?

We are looking for 50-page books on any specific topic covering Excel, Power Pivot, Power BI or any techie topics. Earn generous advances and revenue sharing. Send your idea to Pub@MrExcel.com.